const router = require("koa-router")();
const svgCaptcha = require("svg-captcha");
const formidable = require("koa-formidable"); // 图片处理
const fs = require("fs");
const path = require("path"); // 图片路径
const XLSX = require("xlsx");
const jwt = require("jsonwebtoken");
const myKey = "1234";
const weeksday = [
  "错误",
  "星期一",
  "星期二",
  "星期三",
  "星期四",
  "星期五",
  "星期六",
  "星期日",
];
const daytime = [
  "错误",
  "第一节课",
  "第二节课",
  "第三节课",
  "第四节课",
  "第五节课",
  "第六节课",
  "第七节课",
  "第八节课",
  "第九节课",
];
let user;
fs.readFile("userdata.json", "utf-8", (err, data) => {
  user = JSON.parse(data.toString());
});
let userdata = [];
let wuliudata = [{ id: 0, name: "s", way: "d", address: "f", phone: "g" }];

//没啥用
router.get("/", async (ctx, next) => {
  await ctx.render("index", {
    title: "Hello Koa 2!",
  });
});

router.get("/string", async (ctx, next) => {
  ctx.body = "koa2 string";
});

router.get("/json", async (ctx, next) => {
  ctx.body = {
    title: "koa2 json",
  };
});
//获取今天课程目前没用
router.get("/todayClass", async (ctx, next) => {
  const weekday = ctx.query.weekday;
  let sql = `select * from class where day='${weekday}' ORDER BY time ASC`;
  classdata = await ctx.db.EXCUTE(sql);

  return (ctx.body = classdata);
});
router.post("/upload", async (ctx, next) => {
  let authorization = ctx.header.token;

  if (authorization == undefined) {
    ctx.body = false;
  } else {
    let result = jwt.verify(authorization, myKey);
    if (result.username) {
      ctx.body = { status: "success" };
    } else {
      ctx.body = false;
    }
  }
  return ctx.body;
});
router.get("/test", async (ctx, next) => {
  let sql = "select * from test";
  userdata = await ctx.db.EXCUTE(sql);
  return (ctx.body = userdata);
});
// router.get("/user", async (ctx, next) => {
//   let sql = "select * from userdata";
//   userdata = await ctx.db.EXCUTE(sql);

//   return ctx.body = userdata

// })
router.get("/db", async (ctx, next) => {
  let sql = "select * from userdata";
  let result = await ctx.db.EXCUTE(sql);
  console.log(result);
  ctx.body = result;
});
//验证码
router.get("/svg", async (ctx, next) => {
  console.log(555);
  let options = {
    size: 4,
    noise: 2,
    color: true,
    background: "#666",
    whith: 70,
  };

  let captcha = svgCaptcha.create(options); //字母和数字随机验证码
  return (ctx.body = captcha);
});
//登录
router.post("/user/login", async (ctx, next) => {
  const username = ctx.request.body.username;
  const password = ctx.request.body.password;
  ctx.body = { status: "false", message: "账号或密码错误" };
  let sql = `select * from userdata where loginid='${username}'`;
  userdata = await ctx.db.EXCUTE(sql);
  console.log(userdata);
  if (userdata.length && password == userdata[0].password) {
    let token = jwt.sign({ username: username }, myKey);
    ctx.body = { token: token, status: "ok", userdata: userdata[0] };
  }

  return ctx.body;
});

//上传头像
router.post("/user/uploadAvatar", async (ctx, next) => {
  console.log(ctx.request.files.file, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
  const file = ctx.request.files.file;
  // 读取文件流
  const fileReader = fs.createReadStream(file.filepath);

  const filePath = path.join(__dirname, "../public/image");
  // 组装成绝对路径
  const fileResource = filePath + `/${file.newFilename}.png`;

  /*
   使用 createWriteStream 写入数据，然后使用管道流pipe拼接
  */
  const writeStream = fs.createWriteStream(fileResource);
  // 判断 /static/upload 文件夹是否存在，如果不在的话就创建一个
  if (!fs.existsSync(filePath)) {
    fs.mkdir(filePath, (err) => {
      if (err) {
        throw new Error(err);
      } else {
        fileReader.pipe(writeStream);
        ctx.body = {
          url: "http://localhost:2024/image/" + file.newFilename + ".png",
          code: 0,
          message: "上传成功",
        };
      }
    });
  } else {
    fileReader.pipe(writeStream);
    ctx.body = {
      url: "http://localhost:2024/image/" + file.newFilename + ".png",
      code: 0,
      message: "上传成功",
    };
  }
  return ctx.body;
});

//获取目录菜单
router.get("/user/menu", async (ctx, next) => {
  console.log(ctx.query);
  const power = ctx.query.power;
  let sql = `select * from menu where power='${power}'`;
  menu = await ctx.db.EXCUTE(sql);
  menu.unshift({
    id: 0,
    key: "/home/home",
    label: "首页",
    power: 0,
  });
  return (ctx.body = menu);
});

//获取教师学生课程表
router.get("/teacher/class", async (ctx, next) => {
  const type = ctx.query.type;
  const id = ctx.query.id;
  let sql;
  if (type == 1) {
    sql = `select DISTINCT classname,day,time,address,year,status from class where tid='${id}' `;
  } else {
    sql = `select DISTINCT classname,day,time,address from class where student='${id}'and status in(0,1)`;
  }

  userdata = await ctx.db.EXCUTE(sql);
  return (ctx.body = userdata);
});
router.get("/getSub", async (ctx, next) => {
  let sql = `select * from sub`;
  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", result: result });
});
router.post("/getSub/add", async (ctx, next) => {
  const subname = ctx.request.body.subname;
  let sql = `select * from sub WHERE subname='${subname}'`;
  let result = await ctx.db.EXCUTE(sql);
  ctx.body={status:"false",result:"已存在此专业！"}
  if(result.length==0){
    let sql2 = `INSERT INTO test.sub(subname) VALUES ('${subname}')`;
    let result2 = await ctx.db.EXCUTE(sql2);
    ctx.body={status:"ok",result:"上传成功"}
  }
  return ctx.body;
});

router.post("/getSub/del", async (ctx, next) => {
  const subname = ctx.request.body.subname;

  let sql = `DELETE  from sub WHERE subname='${subname}'`;
  let sql2 = `DELETE FROM class WHERE classname IN (SELECT cname FROM conclass WHERE csub = '${subname}') `;
  let sql3 = `DELETE FROM conclass WHERE csub = '${subname}';
  `;
  let result = await ctx.db.EXCUTE(sql);
  let result2 = await ctx.db.EXCUTE(sql2);
  let result3 = await ctx.db.EXCUTE(sql3);
  ctx.body={status:"ok",result:"删除成功"}
  
  return ctx.body;
});

//获取课程名
router.get("/getClass", async (ctx, next) => {
  const classsub = ctx.query.classsub;
  const classType = ctx.query.classType;
  let sql;
  if (classsub == "所有") {
    sql = `select * from conclass where ctype='${classType}' `;
  } else {
    sql = `select * from conclass where csub='${classsub}' and ctype='${classType}' `;
  }

  classdata = await ctx.db.EXCUTE(sql);

  return (ctx.body = classdata);
});
router.post("/getClass/add", async (ctx, next) => {
  const conclass = ctx.request.body;
  let sql = `select * from conclass where cname='${conclass.cname}' `;
  let result = await ctx.db.EXCUTE(sql);
  ctx.body={status:"false",result:"已存在此课程！"}
  if(result.length==0){
    let sql2 = `INSERT INTO test.conclass(cname,csub,ctype) VALUES('${conclass.cname}','${conclass.csub}','${conclass.ctype}')`;

    let result2 = await ctx.db.EXCUTE(sql2);
    ctx.body={status:"ok",result:"添加成功"}
  }
  

  return ctx.body;
});

router.post("/getClass/delete", async (ctx, next) => {
  const classname = ctx.request.body.classname;
  let sql = `delete from class  WHERE classname='${classname}'`;
  let sql2 = `delete from conclass  WHERE cname='${classname}'`;

  let result = await ctx.db.EXCUTE(sql);
  let result2 = await ctx.db.EXCUTE(sql2);

  return (ctx.body = result);
});

//管理员获取课程详情，修改，添加，删除
router.get("/getClassContext", async (ctx, next) => {
  const cname = ctx.query.cname;
  let sql = `select classname,year,day,time,tid,student,status,allNum,address from class where classname='${cname}'`;
  classdata = await ctx.db.EXCUTE(sql);
  // const uniqueArr = Array.from(new Set(classdata.map(JSON.stringify))).map(JSON.parse);
  const result = [];
  const map = new Map();

  for (const obj of classdata) {
    const key = JSON.stringify({
      classname: obj.classname,
      year: obj.year,
      day: obj.day,
      time: obj.time,
      tid: obj.tid,
      status: obj.status,
      address: obj.address,
      allNum:obj.allNum,
    });

    if (map.has(key)) {
      map.get(key).student.push(obj.student);
    } else {
      const newObj = {
        classname: obj.classname,
        year: obj.year,
        day: obj.day,
        time: obj.time,
        tid: obj.tid,
        status: obj.status,
        address: obj.address,
        student: [obj.student],
        allNum:obj.allNum,
      };
      map.set(key, newObj);
      result.push(newObj);
    }
  }

  // for(let i=0;i<uniqueArr.length;i++){
  //   uniqueArr[i].student= JSON.parse(uniqueArr[i].student)

  // }
  console.log(result);

  return (ctx.body = result);
});

router.post("/classContext/change", async (ctx, next) => {
  const classDetail = ctx.request.body.classDetail;
  const newClassDetail = ctx.request.body.newClassDetail;
  console.log(classDetail);
  console.log("=============");
  console.log(newClassDetail);
  // classDetail.student=JSON.stringify(classDetail.student)

  // newClassDetail.student=JSON.stringify(newClassDetail.student)
  // let sql = `UPDATE class set year='${newClassDetail.year}', day='${newClassDetail.day}',time='${newClassDetail.time}',tid='${newClassDetail.tid}',student='${newClassDetail.student}',status='${newClassDetail.status}',address='${newClassDetail.address}' WHERE year='${classDetail.year}'and day='${classDetail.day}'and time='${classDetail.time}'and tid='${classDetail.tid}'and student='${classDetail.student}'and status='${classDetail.status}'and address='${classDetail.address}'`
  let sql = `delete from class  WHERE year='${classDetail.year}'and day='${classDetail.day}'and time='${classDetail.time}'and tid='${classDetail.tid}'and status='${classDetail.status}'and address='${classDetail.address}'`;
  let result = await ctx.db.EXCUTE(sql);

  let newArray = newClassDetail.student.map((student) => {
    return [
      newClassDetail.classname,
      newClassDetail.year,
      newClassDetail.tid,
      newClassDetail.day,
      newClassDetail.time,
      newClassDetail.allNum,

      newClassDetail.status,
      newClassDetail.address,
      student,
      0,
      0,
    ];
  });
  console.log(newArray, "==================================");
  let sql2 =
    "INSERT INTO test.class(classname,year,tid,day,time,allNum,status,address,student,dailyscore,testscore) VALUES ?";

  let result2 = await ctx.db.EXCUTE(sql2, [newArray]);
  return (ctx.body = result2);
});

router.post("/classContext/add", async (ctx, next) => {
  const title = ctx.request.body.title;
  const newClassDetail = ctx.request.body.newClassDetail;
  console.log(title);
  console.log("=============");
  console.log(newClassDetail);
  // classDetail.student=JSON.stringify(classDetail.student)

  // newClassDetail.student = JSON.stringify(newClassDetail.student)
  // let sql = `INSERT INTO test.class(className,year,day,time,tid,status,student,address) VALUES('${title}','${newClassDetail.year}','${newClassDetail.day}','${newClassDetail.time}','${newClassDetail.tid}','${newClassDetail.status}','${newClassDetail.student}','${newClassDetail.address}')`

  // let result = await ctx.db.EXCUTE(sql);
  let sql1 = `select tid,student,address,status from class where year='${newClassDetail.year}'and day='${newClassDetail.day}'and time='${newClassDetail.time}'`;
  let result1 = await ctx.db.EXCUTE(sql1);
  let result;
  ctx.body = { status: "ok", err: "新增成功" }
  if (result1.length == 0) {
    
    let newArray = newClassDetail.student.map((student) => {
      return [
        title,
        newClassDetail.year,
        newClassDetail.tid,
        newClassDetail.day,
        newClassDetail.time,
        newClassDetail.allNum,

        newClassDetail.status,
        newClassDetail.address,
        student,
      ];
    });
    let sql ="INSERT INTO test.class(classname,year,tid,day,time,allNum,status,address,student) VALUES ?";
    
    result = await ctx.db.EXCUTE(sql, [newArray]);
    console.log("未检测到重复");
  } else {
    for (let i = 0; i < result1.length; i++) {
      console.log(result1[i].status)
      if(result1[i].status==2){
       
        continue;
      }
      else if (result1[i].tid == newClassDetail.tid) {
        console.log("课程教师存在时间冲突");
         (ctx.body = { status: "false", err: "课程教师存在时间冲突" });
      } else if (result1[i].student == newClassDetail.student) {
         (ctx.body = { status: "false", err: "课程学生存在时间冲突" });
      } else if (result1[i].address == newClassDetail.address) {
         (ctx.body = { status: "false", err: "课程地址存在时间冲突" });
      }
    }
    if(ctx.body.status=="ok"){
      let newArray = newClassDetail.student.map((student) => {
        return [
          title,
          newClassDetail.year,
          newClassDetail.tid,
          newClassDetail.day,
          newClassDetail.time,
          newClassDetail.allNum,
          newClassDetail.status,
          newClassDetail.address,
          student,
        ];
      });
      let sql ="INSERT INTO test.class(classname,year,tid,day,time,allNum,status,address,student) VALUES ?";
      ctx.body = { status: "ok", err: "新增成功" }
      result = await ctx.db.EXCUTE(sql, [newArray]);
      console.log("未检测到重复");
    }
  }


  return ctx.body;
});

router.post("/classContext/delete", async (ctx, next) => {
  const classDetail = ctx.request.body;

  // classDetail.student=JSON.stringify(classDetail.student)

  // newClassDetail.student=JSON.stringify(newClassDetail.student)
  // let sql = `UPDATE class set year='${newClassDetail.year}', day='${newClassDetail.day}',time='${newClassDetail.time}',tid='${newClassDetail.tid}',student='${newClassDetail.student}',status='${newClassDetail.status}',address='${newClassDetail.address}' WHERE year='${classDetail.year}'and day='${classDetail.day}'and time='${classDetail.time}'and tid='${classDetail.tid}'and student='${classDetail.student}'and status='${classDetail.status}'and address='${classDetail.address}'`
  let sql = `delete from class  WHERE year='${classDetail.year}'and day='${classDetail.day}'and time='${classDetail.time}'and tid='${classDetail.tid}'and status='${classDetail.status}'and address='${classDetail.address}'`;
  let result = await ctx.db.EXCUTE(sql);

  return (ctx.body = result);
});

//批量添加学生
router.post("/upload/score", async (ctx, next) => {
  // console.log(ctx.request.files.file)
  const file = ctx.request.files.file;
  console.log(file);
  const workbook = XLSX.readFile(file.filepath); // 读取Excel文件
  const worksheet = workbook.Sheets[workbook.SheetNames[0]]; // 获取第一个工作表

  // 解析Excel数据
  const data = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

  let max = 0;
  for (let i = 0; i < data.length; i++) {
    console.log(data[i]);
    if (data[i].length == 0) {
      max = i;
      console.log("filteredData", max);
      break;
    }
  }
  
  const filteredData = data.slice(1, data.length);
  let sql = "INSERT INTO test.student(sid,sname,ssub,syear) VALUES ?";
// let sql2="INSERT INTO test.userdata(loginid,name,ssub,syear) VALUES ?";
  let result = await ctx.db.EXCUTE(sql, [filteredData]);
  console.log(filteredData, max, result); // 在控制台输出解析的数据
  console.log(data);
  ctx.body = data;
  return ctx.body;
});

//添加修改删除用户
router.get("/user", async (ctx, next) => {
  const id = ctx.query.id;
  let sql = `select * from userdata where loginid='${id}'`;
  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", result: result });
});
router.post("/user", async (ctx, next) => {
  const user = ctx.request.body;
  userdata.push(user);
  let sql = `INSERT INTO test.userdata(username,phone,email,address,password,power,loginid,avatar) VALUES('${user.username}','${user.phone}','${user.email}','${user.address}','${user.password}','${user.power}','${user.loginid}','${user.avatar}')`;

  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = "ok");
});

router.post("/user/addUser", async (ctx, next) => {
  console.log(ctx.request.body, "teac");
  const user = ctx.request.body.user;

  let sql = `INSERT INTO test.userdata(name,password,loginid,power,avatar) VALUES('${user.name}','${user.password}','${user.loginid}','${user.power}','${user.avatar}')`;

  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", result: result });
});

router.post("/user/changeUser", async (ctx, next) => {
  const userdata = ctx.request.body;

  let sql = `UPDATE test.userdata SET username= '${userdata.username}',password= '${userdata.password}'WHERE loginid = '${userdata.loginid}'`;
  let result = await ctx.db.EXCUTE(sql);

  return (ctx.body = { status: "ok", result: result });
});

router.post("/deleteuser", async (ctx, next) => {
  const user = ctx.request.body.userdata;
  let sql = `DELETE FROM test.userdata WHERE loginid= '${user.loginid}'`;
  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", result: result });
});

//获取教师，添加修改删除教师
router.get("/teacher", async (ctx, next) => {
  let sql = "select * from teacher";
  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = ctx.body = { status: "ok", result: result });
});
router.post("/teacher", async (ctx, next) => {
  console.log(ctx.request.body, "teac");
  const teacher = ctx.request.body;

  let sql = `INSERT INTO test.teacher(tid,tname,password,subject) VALUES('${teacher.tid}','${teacher.tname}','${teacher.password}','${teacher.subject}')`;

  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", result: result });
});
router.post("/teacher/changeTeacher", async (ctx, next) => {
  console.log(ctx.request.body, "teac");
  const teacher = ctx.request.body;

  let sql = `UPDATE teacher set tname='${teacher.tname}',password='${teacher.password}',subject='${teacher.subject}' WHERE tid='${teacher.tid}'`;

  let result = await ctx.db.EXCUTE(sql);

  return (ctx.body = { status: "ok", result: result });
});
router.post("/teacher/delTeacher", async (ctx, next) => {
  console.log(ctx.request.body, "teac");
  const tid = ctx.request.body.id;

  let sql = `delete from teacher  WHERE tid='${tid}'`;

  let result = await ctx.db.EXCUTE(sql);

  let sql1 = `delete from userdata  WHERE loginid='${tid}'`;
  let sql2 = `UPDATE class set tid='暂定' WHERE tid='${tid}'`;

  let result1 = await ctx.db.EXCUTE(sql1);
  let result2 = await ctx.db.EXCUTE(sql2);
  return (ctx.body = { status: "ok", result: result });
});

//获取，增加修改删除学生
router.get("/stu", async (ctx, next) => {
  let sql = "select * from student";
  wuliudata = await ctx.db.EXCUTE(sql);

  return (ctx.body = wuliudata);
});
router.post("/stu", async (ctx, next) => {
  console.log(ctx.request.body, "teac");
  const stu = ctx.request.body;
  // let sql1 = `INSERT INTO test.student(sid,sname,password,ssub,syear) VALUES('${stu.sid}','${stu.sname}','${stu.password}','${stu.ssub}','${stu.syear}')`

  let sql = `INSERT INTO test.student(sid,sname,password,ssub,syear) VALUES('${stu.sid}','${stu.sname}','${stu.password}','${stu.ssub}','${stu.syear}')`;

  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", id: 0, result: result });
});
router.post("/stu/changeStu", async (ctx, next) => {
  const student = ctx.request.body;

  let sql = `UPDATE student set sname='${student.sname}',password='${student.password}',ssub='${student.ssub}' WHERE sid='${student.sid}'`;

  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", result: result });
});

router.post("/stu/delStu", async (ctx, next) => {
  const sid = ctx.request.body.id;

  let sql = `delete from student  WHERE sid='${sid}'`;
  let sql1 = `delete from userdata  WHERE loginid='${sid}'`;
  let sql2 = `delete from class  WHERE student='${sid}'`;
  let result = await ctx.db.EXCUTE(sql);
  let result1 = await ctx.db.EXCUTE(sql1);
  let result2 = await ctx.db.EXCUTE(sql2);
  return (ctx.body = { status: "ok", result: result });
});
//学生选课
router.post("/checkClass", async (ctx, next) => {
  const classData=ctx.request.body
  const sid = ctx.request.body.sid;
  const selectClass = classData
  const selectArr = [
    selectClass.classname,
    selectClass.year,
    selectClass.tid,
    selectClass.day,
    selectClass.time,
    selectClass.allNum,
    selectClass.status,
    selectClass.address,
    selectClass.id,
  ];

  ctx.body = { status: "ok", result: "选课成功！" }
  let sql1 = `select * from class where student='${classData.id}' and day='${classData.day}'and time='${classData.time}'and  status in(0,1) `;
  let result1 = await ctx.db.EXCUTE(sql1);

  if (result1 != 0) {
    ctx.body = { status: "false", result: "课程冲突" }
  }
  if(classData.student.length==classData.allNum){
    ctx.body = { status: "false", result: "课程已选满，请刷新重试" }
  }
  if(ctx.body.status=="ok"){
    let sql =
    "INSERT INTO class(classname,year,tid,day,time,allNum,status,address,student) VALUES ?";
  let result = await ctx.db.EXCUTE(sql,[[selectArr]]);
  }
  
  
  return ctx.body;
});
router.post("/checkClass/dis", async (ctx, next) => {
  const classData=ctx.request.body

  let sql = `delete from class  WHERE year='${classData.year}'and day='${classData.day}'and time='${classData.time}'and tid='${classData.tid}'and status='${classData.status}'and address='${classData.address}' and student='${classData.id}' `;

  let result1 = await ctx.db.EXCUTE(sql);

  
  return   ctx.body = { status: "ok", result: "退选成功！" }
  ;
});

//教师获取课程所对应的学生
router.get("/getClassStu", async (ctx, next) => {
  console.log(ctx.query);
  const className = ctx.query.classname;
  const tid = ctx.query.tid;
  let sql = `SELECT DISTINCT  student.sid, student.sname, student.ssub, student.syear, class.dailyscore, class.testscore FROM student  JOIN class ON student.sid = class.student where class.year='${ctx.query.year}'and class.day='${ctx.query.day}'and class.time='${ctx.query.time}'and class.tid='${ctx.query.id}'and class.status='${ctx.query.status}'and class.address='${ctx.query.address}'`;
  classStu = await ctx.db.EXCUTE(sql);
  return (ctx.body = classStu);
});
//教师提交学生分数
router.post("/teacher/postScore", async (ctx, next) => {
  const classStu = ctx.request.body.newStuData;
  const selectdClass = ctx.request.body.selectdClass;
  let sql = `UPDATE class set dailyscore='${classStu.dailyscore}', testscore='${classStu.testscore}' WHERE year='${selectdClass.year}'and day='${selectdClass.day}'and time='${selectdClass.time}'and tid='${classStu.tid}'and student='${classStu.sid}'and status='${selectdClass.status}'and address='${selectdClass.address}'`;

  let result = await ctx.db.EXCUTE(sql);
  return (ctx.body = { status: "ok", result: result });
});

//学生获取分数
router.get("/stu/getScore", async (ctx, next) => {
  console.log(ctx.query);
  const id = ctx.query.id;
  const tid = ctx.query.tid;
  let sql = `SELECT DISTINCT teacher.tname,teacher.subject, class.cid,class.classname, class.year,class.dailyscore, class.testscore FROM teacher  JOIN class ON teacher.tid = class.tid where class.student='${id}'and class.status=2`;
  classStu = await ctx.db.EXCUTE(sql);

  return (ctx.body = classStu);
});



module.exports = router;
